﻿Public Class FrmTraHoSoDichVu
    Dim dong As Integer = 0
    Private Sub BatChucNang()
       
        txtTenCongTrinh.Enabled = True
        dtNgayTra.Enabled = True
        dgvDSCongTrinh.Enabled = True
        dgvDSNguoiDaMuon.Enabled = True
        NapDSCongTrinh()
        LoadDSNguoiDangMuonHS()
        btnLuu.Enabled = True

        LamTuoi()
        txtTenCongTrinh.Focus()
    End Sub
    Private Sub TatChucNang()
    
        txtTenCongTrinh.Enabled = False
        dtNgayTra.Enabled = False
        btnLuu.Enabled = False

    End Sub
    Private Sub LamTuoi()
        txtNguoiMuon.Text = ""
        txtDonVi.Text = ""
        txtMucDichMuon.Text = ""
        txtSoHoSo.Text = ""
        txtTenCongTrinh.Text = ""
        dtNgayNhan.Text = ""
        txtHoSoNam.Text = ""
        txtThanhPhanHoSo.Text = ""
    End Sub
    Private Sub NapDSCongTrinh()
        Dim sql As String
        sql = "SELECT CONGTRINH.SoHoSo, CONGTRINH.TenCongTrinh,   CONGTRINH.NgayNhan, CONVERT(CHAR(10), CONGTRINH.NgayNhan, 103) AS NgayNhan, CONGTRINH.HoSoNam, CONGTRINH.ThanhPhanHoSo, " &
              "TENHUYEN.T_Huyen, TENXA.TenXa, DMPhong.TenPhong, DMKe.TenKe, DMKho.TenKho, DMTinhTrang.TenTinhTrang " &
              "FROM CONGTRINH LEFT OUTER JOIN " &
              "DMPhong ON congtrinh.MaPhongID = DMPhong.MaPhongID LEFT OUTER JOIN " &
              "DMKe ON CONGTRINH.MaKeID = DMKe.MaKeID LEFT OUTER JOIN " &
              "DMKho ON CONGTRINH.MaKhoID = DMKho.maKhoID LEFT OUTER JOIN " &
              "DMTinhTrang ON CONGTRINH.MaTinhTrangID = DMTinhTrang.MaTinhTrangID LEFT OUTER JOIN " &
              "TENXA ON CONGTRINH.MaXa = TENXA.MaXa LEFT OUTER JOIN " &
              "TENHUYEN ON TENHUYEN.maH = TENXA.MaHuyen " &
              "WHERE CONGTRINH.MATINHTRANGID =2 " &
              "AND TENCONGTRINH  like N'%" & txtTenCongTrinh.Text.Trim & "%'"
        dgvDSCongTrinh.DataSource = LoadThongTin(sql)
        dgvDSCongTrinh.Refresh()
    End Sub
    Private Sub LoadDSNguoiDangMuonHS()
        Dim sql As String
        sql = "SELECT DISTINCT NGUOIMUON, DONVI FROM PHIEUMUON_HSDV, CONGTRINH WHERE CONGTRINH.SoHoSo = PHIEUMUON_HSDV.SoHoSo AND CONGTRINH.MATINHTRANGID =2 AND PHIEUMUON_HSDV.NgayTra IS NULL"
        dgvDSNguoiDaMuon.DataSource = LoadThongTin(sql)
        dgvDSNguoiDaMuon.Refresh()
    End Sub

    Private Sub FrmTraHoSoDichVu_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        LoadHuyen(cmbHuyen)
        LoadXa(Convert.ToInt32(cmbHuyen.SelectedValue.ToString), cmbXa)
        LoadPhong(cmbPhong)
        LoadKe(cmbKe)
        LoadKho(cmbKho)
        LoadHinhThuc(cmbHinhThuc)
        LoadTinhTrang(cmbTinhTrang)

        dtNgayMuon.Text = Date.Today()
        dtNgayNhan.Text = Date.Today()
        dtNgayTra.Text = Date.Today()
        'LoadDSNguoiDangMuonHS()
        'NapDSCongTrinh()
        TatChucNang()
        btnNhap.Focus()
        dgvDSCongTrinh.RowsDefaultCellStyle.BackColor = Color.Beige
        dgvDSCongTrinh.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        dgvDSNguoiDaMuon.RowsDefaultCellStyle.BackColor = Color.Beige
        dgvDSNguoiDaMuon.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure

    End Sub

    Private Sub cmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbHuyen.SelectedIndexChanged
        LoadXa(Convert.ToInt32(cmbHuyen.SelectedValue.ToString), cmbXa)
    End Sub

    Private Sub btnNhap_Click(sender As Object, e As EventArgs) Handles btnNhap.Click
        LamTuoi()
        BatChucNang()
        '  cmbTinhTrang.SelectedValue = 1
        dtNgayTra.Text = Date.Today()
    End Sub
    Private Function SoSanhTenCongTrinh() As Boolean
        Dim sql As String
        sql = "Select TenCongTrinh From CONGTRINH Where TenCongTrinh like N'" & txtTenCongTrinh.Text.Trim & "' AND SoHoSo = " & Int32.Parse(txtSoHoSo.Text) & ""
        Dim tb As DataTable = LoadThongTin(sql)
        If tb.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Private Sub btnLuu_Click(sender As Object, e As EventArgs) Handles btnLuu.Click
        Dim sql As String
        Dim tb As New DataTable

        'Kiem tra Ten cong trinh khong duoc rong
        If (txtTenCongTrinh.Text.Trim = "") Then
            MessageBox.Show("Tên Công trình không được rỗng ")
            txtTenCongTrinh.Focus()
            Return
        End If

        If dgvDSCongTrinh.RowCount = 0 Then
            MessageBox.Show("Hồ sơ công trình không có hoặc chưa cho mượn", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If
        'Kiem tra chua chon Ho so cong trinh de muon
        If (txtSoHoSo.Text.Trim = "") Then
            MessageBox.Show("Chưa chọn Hồ sơ công trình để mượn. Hoặc hồ sơ công trình không có ")

            Return
        End If

        If SoSanhTenCongTrinh() = False Then
            MessageBox.Show("Tên công trình không hợp lệ", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

        'Kiem tra Ngay tra ho so phai lon hon ngay muon va nho hon ngay hien hanh
        If (dtNgayTra.Value < dtNgayMuon.Value Or dtNgayTra.Value > Date.Today) Then
            MessageBox.Show("Ngày trả không hợp lệ ")
            dtNgayTra.Focus()
            Return
        End If


        Dim NgayTra As String
        NgayTra = String.Format("{0:MM/dd/yyyy}", dtNgayTra.Value)

        sql = "UPDATE PHIEUMUON_HSDV  SET NGAYTRA = '" & NgayTra & "' WHERE SoHoSo = " & Int32.Parse(txtSoHoSo.Text) & " AND NgayTra IS NULL"

        Dim flag As Boolean
        flag = ThaoTacDuLieu(sql)

        If (flag = True) Then
            MessageBox.Show("Lưu thông tin trả hồ sơ thành công")
            CapNhatTinhTrang()
            LuuThongTinTraHoSo()
            '   TatChucNang()
            LamTuoi()
            NapDSCongTrinh()
            LoadDSNguoiDangMuonHS()
        End If

    End Sub
    Private Function GetSoPhieuMuon() As Integer
        Dim sql As String
        Dim NgayTra As String
        NgayTra = String.Format("{0:MM/dd/yyyy}", dtNgayTra.Value)

        sql = "SELECT MAX(SoPhieuMuon) From PHIEUMUON_HSDV  WHERE SoHoSo = " & Int32.Parse(txtSoHoSo.Text) & " AND NgayTra = '" & NgayTra & "' "
        Return LoadThongTin(sql).Rows(0)(0).ToString
    End Function
    Private Sub CapNhatTinhTrang()
        Dim sql As String
        sql = "UPDATE CONGTRINH SET MATINHTRANGID = 1 WHERE SOHOSO = " & Int32.Parse(txtSoHoSo.Text) & ""
        Dim t As Boolean
        t = ThaoTacDuLieu(sql)
    End Sub
    Private Sub LuuThongTinTraHoSo()
        Dim SoPhieuMuon = GetSoPhieuMuon()
        Dim sql As String
        sql = "INSERT INTO QL_TraHoSoDichVu (SoPhieuMuon,NguoiNhanTra) VALUES (" & SoPhieuMuon & ", '" & ModuleTenDangNhap.m_tennhap & "')"
        Dim t As Boolean
        t = ThaoTacDuLieu(sql)
    End Sub

    Private Sub btnThoat_Click(sender As Object, e As EventArgs) Handles btnThoat.Click
        Me.Close()
    End Sub
    Private Sub dgvDSCongTrinh_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvDSCongTrinh.CellClick

        dong = e.RowIndex

        'Kiem tra phai dong tieu de ko
        If (dong = -1) Then
            Return
        End If

        'Dua du lieu tu DataGridView len cac textbox va combox
        txtSoHoSo.Text = dgvDSCongTrinh.Rows(dong).Cells("SoHoSo").Value.ToString()
        txtTenCongTrinh.Text = dgvDSCongTrinh.Rows(dong).Cells("TenCongTrinh").Value.ToString()

        dtNgayNhan.Text = String.Format("{0:dd/MM/yyyy}", dgvDSCongTrinh.Rows(dong).Cells("NgayNhan").Value.ToString())

        txtHoSoNam.Text = dgvDSCongTrinh.Rows(dong).Cells("HoSoNam").Value.ToString()
        txtThanhPhanHoSo.Text = dgvDSCongTrinh.Rows(dong).Cells("ThanhPhanHoSo").Value.ToString()
        cmbHuyen.Text = dgvDSCongTrinh.Rows(dong).Cells("T_Huyen").Value.ToString()
        cmbXa.Text = dgvDSCongTrinh.Rows(dong).Cells("TenXa").Value.ToString()
        cmbPhong.Text = dgvDSCongTrinh.Rows(dong).Cells("TenPhong").Value.ToString()
        cmbKe.Text = dgvDSCongTrinh.Rows(dong).Cells("TenKe").Value.ToString()
        cmbKho.Text = dgvDSCongTrinh.Rows(dong).Cells("TenKho").Value.ToString()
        cmbTinhTrang.Text = dgvDSCongTrinh.Rows(dong).Cells("TenTinhTrang").Value.ToString()

        '========= Load Thong Tin Nguoi Muon
        Dim sql As String
        sql = "SELECT NguoiMuon, PHIEUMUON_HSDV.NgayMuon, DonVi, MucDichMuon, MaHinhThucID FROM PHIEUMUON_HSDV, CONGTRINH WHERE  CONGTRINH.SoHoSo = PHIEUMUON_HSDV.SoHoSo AND PHIEUMUON_HSDV.SoPhieuMuon = (SELECT MAX(PHIEUMUON_HSDV.SoPhieuMuon) FROM PHIEUMUON_HSDV WHERE PHIEUMUON_HSDV.SoHoSo = " & Int32.Parse(txtSoHoSo.Text) & ") AND CONGTRINH.MATINHTRANGID =2 "
        Dim tb = LoadThongTin(sql)

        txtNguoiMuon.Text = tb.Rows(0)("NguoiMuon")
        dtNgayMuon.Text = tb.Rows(0)("NgayMuon")
        txtDonVi.Text = tb.Rows(0)("DonVi")
        txtMucDichMuon.Text = tb.Rows(0)("MucDichMuon")
        cmbHinhThuc.SelectedValue = tb.Rows(0)("MaHinhThucID")
    End Sub

    Private Sub txtTenCongTrinh_KeyUp(sender As Object, e As KeyEventArgs) Handles txtTenCongTrinh.KeyUp
        NapDSCongTrinh()
    End Sub

    Private Sub dgvDSNguoiDaMuon_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvDSNguoiDaMuon.CellClick
        dong = e.RowIndex

        'Kiem tra phai dong tieu de ko
        If (dong = -1) Then
            Return
        End If

        Dim sql As String
        sql = "SELECT CONGTRINH.SoHoSo, CONGTRINH.TenCongTrinh,   CONGTRINH.NgayNhan, CONVERT(CHAR(10), CONGTRINH.NgayNhan, 103) " &
              " AS NgayNhan, CONGTRINH.HoSoNam, CONGTRINH.ThanhPhanHoSo, " &
              "TENHUYEN.T_Huyen, TENXA.TenXa, DMPhong.TenPhong, DMKe.TenKe, DMKho.TenKho, DMTinhTrang.TenTinhTrang " &
              "FROM CONGTRINH LEFT OUTER JOIN " &
              "DMPhong ON congtrinh.MaPhongID = DMPhong.MaPhongID LEFT OUTER JOIN " &
              "DMKe ON CONGTRINH.MaKeID = DMKe.MaKeID LEFT OUTER JOIN " &
              "DMKho ON CONGTRINH.MaKhoID = DMKho.maKhoID LEFT OUTER JOIN " &
              "DMTinhTrang ON CONGTRINH.MaTinhTrangID = DMTinhTrang.MaTinhTrangID LEFT OUTER JOIN " &
              "TENXA ON CONGTRINH.MaXa = TENXA.MaXa LEFT OUTER JOIN " &
              "TENHUYEN ON TENHUYEN.maH = TENXA.MaHuyen LEFT OUTER JOIN " &
              "PHIEUMUON_HSDV ON PHIEUMUON_HSDV.SoHoSo = CONGTRINH.SoHoSo " &
              "WHERE CONGTRINH.MATINHTRANGID = 2 " &
              "AND CONGTRINH.TENCONGTRINH  like N'%" & txtTenCongTrinh.Text.Trim & "%' " &
              "AND PHIEUMUON_HSDV.NGUOIMUON like N'" & dgvDSNguoiDaMuon.Rows(dong).Cells("NguoiMuon").Value.ToString() & "' " &
              "AND PHIEUMUON_HSDV.NgayTra IS NULL "
        dgvDSCongTrinh.DataSource = LoadThongTin(sql)
        dgvDSCongTrinh.Refresh()
    End Sub

    Private Sub dgvDSCongTrinh_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvDSCongTrinh.CellContentClick

    End Sub

    Private Sub dgvDSCongTrinh_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles dgvDSCongTrinh.RowPostPaint
        ' xuat so thu tu tu dong cho datagridview
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < dgvDSCongTrinh.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If dgvDSCongTrinh.RowHeadersWidth < Size.Width + 20 Then dgvDSCongTrinh.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub dgvDSNguoiDaMuon_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles dgvDSNguoiDaMuon.RowPostPaint
        ' xuat so thu tu tu dong cho datagridview
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < dgvDSNguoiDaMuon.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If dgvDSNguoiDaMuon.RowHeadersWidth < Size.Width + 20 Then dgvDSNguoiDaMuon.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub
End Class